package com.gen.jpa.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.cj.util.StringUtils;
import com.gen.jpa.entity.*;


import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库表格生成工具类
 */
public class DbUtils {

    private DbUtils() {
    }

    private static ComboPooledDataSource cpds;
    private static Connection conn;
    private static Statement sta;
    private static ResultSet rs;

    /**
     * 构建数据库连接
     *
     * @return
     * @throws SQLException
     * @throws PropertyVetoException
     */
    private static Connection buildConnection() throws SQLException, PropertyVetoException {
       if (cpds==null){
        cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://192.168.1.37:3306/fips?serverTimezone=GMT%2B8&characterEncoding=utf8&useSSL=false");
        cpds.setUser("admin");
        cpds.setPassword("123456");
        // 得到一个Connection
        conn = cpds.getConnection();
        return conn;
       }
        return conn;
    }

    /**
     * 执行sql脚本
     * @param sql
     * @return
     * @throws SQLException
     * @throws PropertyVetoException
     */
    private static ResultSet executeScript(String sql) throws SQLException, PropertyVetoException {
        conn = buildConnection();
        sta = conn.createStatement();
        rs = sta.executeQuery(sql);
        return rs;
    }

    /**
     * 关闭连接
     * @throws SQLException
     */
    public static void close() throws SQLException {
        rs.close();
        sta.close();
        conn.close();
        cpds.close();
    }

    /**
     * 封装表名、列名、列类型（初始化全量生成）
     * @param tablePrefix 表前缀，如传了表前缀生成的实体将忽略掉前缀部分
     * @return
     * @throws PropertyVetoException
     * @throws SQLException
     */
    public static List<TableModel> buildTableModelList(String tablePrefix) throws PropertyVetoException, SQLException {
        List<TableModel> tableList = new ArrayList<>();
        ResultSet rs = executeScript("show tables");
        while (rs.next()) {
            //表名
            String tableName = rs.getString(1);
            String entityName = tableName;
            if (!StringUtils.isNullOrEmpty(tablePrefix) && tablePrefix.startsWith(tablePrefix)) {
                entityName = entityName.substring(tablePrefix.length());
            }
            if ("hibernate_sequence".equals(tableName)) {
                continue;
            }
            tableList.add(new TableModel(tableName, toCamelCase(entityName, true), getTableComment(tableName), getColumnList(tableName)));
        }
        rs.close();
        return tableList;
    }

    /**
     * 根据表格名封装表名、列名、列类型
     * @param tablePrefix 表前缀，如传了表前缀生成的实体将忽略掉前缀部分
     * @param tableName 数据库表名称
     * @return
     * @throws PropertyVetoException
     * @throws SQLException
     */
    public static TableModel buildTableModel(String tablePrefix, String tableName) throws PropertyVetoException, SQLException {
        tableName = tableName.toLowerCase();
        String entityName = tableName;
        if (!StringUtils.isNullOrEmpty(tablePrefix) && tablePrefix.startsWith(tablePrefix)) {
            entityName = entityName.substring(tablePrefix.length());
        }
        String tableComment = getTableComment(tableName);
        List<ColumnModel> columnList = getColumnList(tableName);

        return new TableModel(tableName, toCamelCase(entityName, true), tableComment, columnList);
    }
  public static List<TableModel> buildTableModel(String tablePrefix, List<String> tableName) throws PropertyVetoException, SQLException {
      List<TableModel> result = new ArrayList<>();
      if(tableName!=null&&tableName.size()>0){
          for (String table : tableName) {
              TableModel tableModel = buildTableModel(tablePrefix, table);
              result.add(tableModel);
          }
      }
    return result;
    }

    /**
     * 获取指定表格的描述
     *
     * @param tableName 数据库表名称
     * @return
     * @throws PropertyVetoException
     * @throws SQLException
     */
    private static String getTableComment(String tableName) throws PropertyVetoException, SQLException {
        String tableDesc = "";
        ResultSet rs = executeScript("SHOW TABLE STATUS WHERE Name = '" + tableName + "'");
        while (rs.next()) {
            //表名
            tableDesc = rs.getString("Comment");
        }
        rs.close();
        return tableDesc;
    }

    /**
     * 根据表名来返回该表对应列名和列类型
     *
     * @param tableName 数据库表名称
     * @return
     * @throws SQLException
     * @throws PropertyVetoException
     */
    private static List<ColumnModel> getColumnList(String tableName) throws PropertyVetoException, SQLException {
        List<ColumnModel> columnList = new ArrayList<>();
        ResultSet rs = executeScript("SELECT isc.COLUMN_NAME,isc.DATA_TYPE,isc.COLUMN_KEY,isc.COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS isc where isc.TABLE_NAME = '" + tableName + "'");
        while (rs.next()) {
            boolean isPrimary = false;
            String columnName = rs.getString("COLUMN_NAME").toLowerCase();
            String dataType = rs.getString("DATA_TYPE");
            String columnKey = rs.getString("COLUMN_KEY");
            if (!StringUtils.isNullOrEmpty(columnKey) && "PRI".equals(columnKey)) {
                isPrimary = true;
            }
            String columnComment = rs.getString("COLUMN_COMMENT");
            ColumnModel columnModel = new ColumnModel();
            columnModel.setColumnName(columnName);
            columnModel.setHumpColumnName(toCamelCase(columnName, false));
            columnModel.setColumnType(dataType);
            columnModel.setJavaType(dataType);
            columnModel.setColumnDesc(columnComment);
            columnModel.setPrimary(isPrimary);
            columnList.add(columnModel);
        }
        rs.close();
        return columnList;
    }

    /**
     * 数据库列名转驼峰命名
     *
     * @param fieldName 字段名称
     * @param allConvert 是否全部转换（控制首字母是否转大小写）
     * @return
     */
    private static String toCamelCase(String fieldName, boolean allConvert) {
        String[] words = fieldName.split("_");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < words.length; i++) {
            if (i == 0 && !allConvert) {
                sb.append(words[i]);
            } else {
                String word = words[i].substring(0, 1).toUpperCase() + words[i].substring(1);
                sb.append(word);
            }
        }
        return sb.toString();
    }
}